package xworker.dataObject.db;

import org.xmeta.ActionContext;
import org.xmeta.Thing;
import org.xmeta.util.UtilString;
import xworker.dataObject.db.dialets.*;
import xworker.dataObject.query.ConditionValue;
import xworker.dataObject.query.QueryConfig;
import xworker.dataObject.utils.DbUtil;
import xworker.lang.executor.Executor;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class DbDataObjectUtils {
    private static final String TAG = DbDataObjectUtils.class.getName();

    private static final HashMap<String, String> jdbcSubProtocolToPlatform = new HashMap<>();
    private static final HashMap<String, String> jdbcDriverToPlatform = new HashMap<>();

    private static final Map<String, SqlDialet> sqlDialetMap = new HashMap<>();
    static{
        sqlDialetMap.put("oracle", new OracleDialet());
        sqlDialetMap.put("Oracle", new OracleDialet());
        sqlDialetMap.put("derby", new DerbyDialet());
        sqlDialetMap.put("Derby", new DerbyDialet());
        sqlDialetMap.put("mysql", new MysqlDialet());
        sqlDialetMap.put("MySql", new MysqlDialet());
        sqlDialetMap.put("sqlserver2005", new SqlServer2005Dialet());
        sqlDialetMap.put("MsSql", new SqlServer2005Dialet());
        sqlDialetMap.put("sqlserver", new SqlServer2005Dialet());
        sqlDialetMap.put("sqlite", new MysqlDialet());
        sqlDialetMap.put("DB2", new DB2Dialet());
        sqlDialetMap.put("Firebird", new FirebirdDialet());
        sqlDialetMap.put("HsqlDb", new HsqlDbDialet());
        sqlDialetMap.put("Interbase", new InterbaseDialet());
        sqlDialetMap.put("SapDB", new SapDBDialet());
        sqlDialetMap.put("PostgreSql", new PostgreSqlDialet());

        jdbcSubProtocolToPlatform.put("axiondb", "Axion");
        jdbcSubProtocolToPlatform.put("db2j:net", "Cloudscape");
        jdbcSubProtocolToPlatform.put("cloudscape:net", "Cloudscape");
        jdbcSubProtocolToPlatform.put("db2", "DB2");
        jdbcSubProtocolToPlatform.put("db2os390", "DB2");
        jdbcSubProtocolToPlatform.put("db2os390sqlj", "DB2");
        jdbcSubProtocolToPlatform.put("as400", "DB2");
        jdbcSubProtocolToPlatform.put("datadirect:db2", "DB2");
        jdbcSubProtocolToPlatform.put("inetdb2", "DB2");
        jdbcSubProtocolToPlatform.put("derby", "Derby");
        jdbcSubProtocolToPlatform.put("firebirdsql", "Firebird");
        jdbcSubProtocolToPlatform.put("hsqldb", "HsqlDb");
        jdbcSubProtocolToPlatform.put("interbase", "Interbase");
        jdbcSubProtocolToPlatform.put("sapdb", "SapDB");
        jdbcSubProtocolToPlatform.put("mckoi", "McKoi");
        jdbcSubProtocolToPlatform.put("microsoft:sqlserver", "MsSql");
        jdbcSubProtocolToPlatform.put("sqlserver", "MsSql");
        jdbcSubProtocolToPlatform.put("sqljdbc", "MsSql");
        jdbcSubProtocolToPlatform.put("datadirect:sqlserver", "MsSql");
        jdbcSubProtocolToPlatform.put("inetdae", "MsSql");
        jdbcSubProtocolToPlatform.put("inetdae6", "MsSql");
        jdbcSubProtocolToPlatform.put("inetdae7", "MsSql");
        jdbcSubProtocolToPlatform.put("inetdae7a", "MsSql");
        jdbcSubProtocolToPlatform.put("inetpool:inetdae", "MsSql");
        jdbcSubProtocolToPlatform.put("inetpool:inetdae6", "MsSql");
        jdbcSubProtocolToPlatform.put("inetpool:inetdae7", "MsSql");
        jdbcSubProtocolToPlatform.put("inetpool:inetdae7a", "MsSql");
        jdbcSubProtocolToPlatform.put("inetpool:jdbc:inetdae", "MsSql");
        jdbcSubProtocolToPlatform.put("inetpool:jdbc:inetdae6", "MsSql");
        jdbcSubProtocolToPlatform.put("inetpool:jdbc:inetdae7", "MsSql");
        jdbcSubProtocolToPlatform.put("inetpool:jdbc:inetdae7a", "MsSql");
        jdbcSubProtocolToPlatform.put("JSQLConnect", "MsSql");
        jdbcSubProtocolToPlatform.put("jtds:sqlserver", "MsSql");
        jdbcSubProtocolToPlatform.put("mysql", "MySQL");
        jdbcSubProtocolToPlatform.put("oracle:thin", "Oracle");
        jdbcSubProtocolToPlatform.put("oracle:oci8", "Oracle");
        jdbcSubProtocolToPlatform.put("oracle:dnldthin", "Oracle");
        jdbcSubProtocolToPlatform.put("datadirect:oracle", "Oracle");
        jdbcSubProtocolToPlatform.put("inetora", "Oracle");
        jdbcSubProtocolToPlatform.put("postgresql", "PostgreSql");
        jdbcSubProtocolToPlatform.put("sybase:Tds", "Sybase");
        jdbcSubProtocolToPlatform.put("datadirect:sybase", "Sybase");
        jdbcSubProtocolToPlatform.put("inetsyb", "Sybase");
        jdbcSubProtocolToPlatform.put("inetpool:jdbc:inetsyb", "Sybase");
        jdbcSubProtocolToPlatform.put("jtds:sybase", "Sybase");
        jdbcDriverToPlatform.put("org.axiondb.jdbc.AxionDriver", "Axion");
        jdbcDriverToPlatform.put("com.ibm.db2.jcc.DB2Driver", "DB2");
        jdbcDriverToPlatform.put("COM.ibm.db2.jdbc.app.DB2Driver", "DB2");
        jdbcDriverToPlatform.put("COM.ibm.db2os390.sqlj.jdbc.DB2SQLJDriver", "DB2");
        jdbcDriverToPlatform.put("com.ibm.as400.access.AS400JDBCDriver", "DB2");
        jdbcDriverToPlatform.put("com.ddtek.jdbc.db2.DB2Driver", "DB2");
        jdbcDriverToPlatform.put("com.inet.drda.DRDADriver", "DB2");
        jdbcDriverToPlatform.put("org.apache.derby.jdbc.EmbeddedDriver", "Derby");
        jdbcDriverToPlatform.put("org.apache.derby.jdbc.ClientDriver", "Derby");
        jdbcDriverToPlatform.put("org.firebirdsql.jdbc.FBDriver", "Firebird");
        jdbcDriverToPlatform.put("org.hsqldb.jdbcDriver", "HsqlDb");
        jdbcDriverToPlatform.put("interbase.interclient.Driver", "Interbase");
        jdbcDriverToPlatform.put("com.sap.dbtech.jdbc.DriverSapDB", "SapDB");
        jdbcDriverToPlatform.put("com.mckoi.JDBCDriver", "McKoi");
        jdbcDriverToPlatform.put("com.microsoft.jdbc.sqlserver.SQLServerDriver", "MsSql");
        jdbcDriverToPlatform.put("com.microsoft.sqlserver.jdbc.SQLServerDriver", "MsSql");
        jdbcDriverToPlatform.put("com.ddtek.jdbc.sqlserver.SQLServerDriver", "MsSql");
        jdbcDriverToPlatform.put("com.inet.tds.TdsDriver", "MsSql");
        jdbcDriverToPlatform.put("com.jnetdirect.jsql.JSQLDriver", "MsSql");
        jdbcDriverToPlatform.put("com.mysql.jdbc.Driver", "MySQL");
        jdbcDriverToPlatform.put("org.gjt.mm.mysql.Driver", "MySQL");
        jdbcDriverToPlatform.put("oracle.jdbc.driver.OracleDriver", "Oracle");
        jdbcDriverToPlatform.put("oracle.jdbc.dnlddriver.OracleDriver", "Oracle");
        jdbcDriverToPlatform.put("com.ddtek.jdbc.oracle.OracleDriver", "Oracle");
        jdbcDriverToPlatform.put("com.inet.ora.OraDriver", "Oracle");
        jdbcDriverToPlatform.put("org.postgresql.Driver", "PostgreSql");
        jdbcDriverToPlatform.put("com.sybase.jdbc2.jdbc.SybDriver", "Sybase");
        jdbcDriverToPlatform.put("com.sybase.jdbc.SybDriver", "Sybase");
        jdbcDriverToPlatform.put("com.ddtek.jdbc.sybase.SybaseDriver", "Sybase");
        jdbcDriverToPlatform.put("com.inet.syb.SybDriver", "Sybase");
    }

    /**
     * 返回一个数据库数据对象定义的属性列表，即对应数据表（或查询结果）中的字段，排除非数据字段(dataField)。
     * @param dbDataObject 数据库数据对象模型
     * @return 属性列表
     */
    public static List<Thing> getAttributes(Thing dbDataObject){
        List<Thing> attributes = dbDataObject.getChilds("attribute");
        for(int i=0; i<attributes.size(); i++){
            //过滤非数据字段和不需要显示的字段
            if(!attributes.get(i).getBoolean("dataField")){
                attributes.remove(i);
                i--;
            }
        }

        return attributes;
    }

    public static void addWhereSql(Thing self, QueryConfig queryConfig, boolean hasValue, StringBuilder sb){
        //查询条件
        String clause = queryConfig.getConditionSql(hasValue);
        if(clause != null && !clause.isEmpty()){
            int whereIndex = sb.indexOf("where");
            if(whereIndex == -1) {
                sb.append(" where ");
                sb.append(clause);
            }else {
                int rightKuoHaoIndex = sb.indexOf(")");
                if(rightKuoHaoIndex > whereIndex) {
                    //where在括号内
                    sb.append(" where ");
                    sb.append(clause);
                }else {
                    //where末尾的情况
                    sb.append(" and ");
                    sb.append("clause");
                }
            }
        }

        String sqlAppend = self.getStringBlankAsNull("sqlAppend");
        if(sqlAppend != null) {
            sb.append(" ");
            sb.append(sqlAppend);
        }

        String groups = queryConfig.getGroupsSql();
        if(groups != null && !groups.isEmpty()){
            sb.append(" group by ");
            sb.append(groups);
        }

        String orders = queryConfig.getOrdersSql();
        if(orders != null && !orders.isEmpty()){
            sb.append(" order by ");
            sb.append(orders);
        }else{
            String storeSortField = self.getStringBlankAsNull("storeSortField");
            String storeSortDir = self.getStringBlankAsNull("storeSortDir");
            if(storeSortField != null && storeSortDir != null){
                sb.append(" order by ");
                sb.append(storeSortField);
                sb.append(" ");
                sb.append(storeSortDir);
            }
        }
    }

    public static String getCountSql(Thing self, QueryConfig queryConfig, ActionContext actionContext, boolean hasValue){
        StringBuilder sb = new StringBuilder();

        String querySql= self.getStringBlankAsNull("querySql");
        if(self.getBoolean("keepQuerySql") && querySql != null){
            sb.append(querySql);
        }else{
            sb.append("select count(*) from ");

            //表名或子查询
            String tableName = UtilString.getString(self, "tableName", actionContext);//self.getString("tableName");

            if(querySql != null && !"".equals(querySql)){
                if(querySql.trim().startsWith("(")){
                    tableName = querySql;
                }else{
                    tableName = "(" + querySql + ") t";
                }
            }
            sb.append(tableName);
        }

        addWhereSql(self, queryConfig, hasValue, sb);
        return sb.toString();
    }

    public static String getQuerySql(Thing self, List<Thing> attributes, QueryConfig queryConfig, ActionContext actionContext, boolean hasValue){
        StringBuilder sb = new StringBuilder();

        String querySql= self.getStringBlankAsNull("querySql");
        if(self.getBoolean("keepQuerySql") && querySql != null){
            sb.append(querySql);
        }else{
            sb.append("select ");
            int atrCount = 0;
            //要选择的字段列表
            for (Thing attribute : attributes) {
                if (atrCount > 0) {
                    sb.append(",");
                }

                atrCount++;
                String fieldName = getFieldName(attribute);
                sb.append(fieldName);
            }

            //表名或子查询
            String tableName = UtilString.getString(self, "tableName", actionContext);//self.getString("tableName");

            if(querySql != null && !"".equals(querySql)){
                if(querySql.trim().startsWith("(")){
                    tableName = querySql;
                }else{
                    tableName = "(" + querySql + ") t";
                }
            }
            sb.append(" from ");
            sb.append(tableName);
        }

        addWhereSql(self, queryConfig, hasValue, sb);

        return sb.toString();
    }

    /**
     * 通过属性模型获取对应数据表的字段名。首先获取模型sql属性，其次是fieldName属性，最后使用模型的name属性。
     */
    public static String getFieldName(Thing attribute){
        String fieldName = attribute.getString("sql");
        if(fieldName == null || "".equals(fieldName)){
            fieldName = attribute.getString("fieldName");
        }
        if(fieldName == null || "".equals(fieldName)){
            return attribute.getString("name");
        }

        return fieldName;
    }

    /**
     * 设置PreparedStatement的条件，返回设置后的条件索引位置。首先根据查询条件和属性定义获取条件值列表，值列表一般包含了值的类型，如果没有值类型默认作为字符串类型。
     *
     * @param pst 要设置条件值的PreparedStatement
     * @param index 条件的索引位置，偏移量
     * @param values 查询条件列表
     * @return 设置后的条件索引位置，偏移量
     * @throws SQLException 异常
     */
    public static int setStatementParams(PreparedStatement pst, int index, List<ConditionValue> values) throws SQLException {
        for(int i=0; i<values.size(); i++){
            ConditionValue value = values.get(i);
            String type = value.getType();
            if(type == null){
                Executor.warn(TAG, "Can not find determine condition value type, condition=" + value);
                type = "string";
            }

            DbUtil.setParameterValue(pst, index + i, type , value.getValue());
        }

        return index + values.size();
    }

    /**
     * 根据数据库连接和类型指示获取SqlDialet。
     *
     * @param con 数据库连接
     * @param type 数据库类型，通常是DataSource模型中设置的类型
     * @return SqlDialet
     * @throws SQLException 当使用数据库连接判断数据库类型时发生的异常
     */
    public static SqlDialet getSqlDialet(Connection con, String type) throws SQLException {
        SqlDialet sqlDialet = sqlDialetMap.get(type);
        if(sqlDialet == null && con != null){
            DatabaseMetaData metaData = con.getMetaData();
            type = determineDatabaseType(metaData.getDriverName(), metaData.getURL());
            if(type != null){
                sqlDialet = sqlDialetMap.get(type);
            }
        }

        return sqlDialet;
    }

    /**
     * 注册一种SqlDialet。
     *
     * @param type 类型，通常是DataSource模型里设置的数据库类型
     * @param sqlDialet SqlDialet
     */
    public static void registSqlDialet(String type, SqlDialet sqlDialet){
        sqlDialetMap.put(type, sqlDialet);
    }

    public static String determineDatabaseType(String driverName, String jdbcConnectionUrl) {
        if (jdbcDriverToPlatform.containsKey(driverName)) {
            return jdbcDriverToPlatform.get(driverName);
        } else if (jdbcConnectionUrl == null) {
            return null;
        } else {
            Iterator<Map.Entry<String, String>> it = jdbcSubProtocolToPlatform.entrySet().iterator();

            Map.Entry<String, String> entry;
            String curSubProtocol;
            do {
                if (!it.hasNext()) {
                    return null;
                }

                entry = it.next();
                curSubProtocol = "jdbc:" + entry.getKey() + ":";
            } while(!jdbcConnectionUrl.startsWith(curSubProtocol));

            return entry.getValue();
        }
    }
}
